CREATE TABLE [dbo].[tb_Permisos]
(
[permiso] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[idPermiso] [int] NOT NULL IDENTITY(1, 1),
[Descripcion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
TRIGGER PARA ELIMINAR LOS ROLES ASOCIADOS A UN PERMISO QUE HA SIDO BORRADO
*/

CREATE TRIGGER [dbo].[delete_permiso] ON [dbo].[tb_Permisos]
FOR DELETE 
AS
SET NOCOUNT ON
DELETE tb_RelPermisosRoles FROM tb_RelPermisosRoles WHERE idPermiso in (SELECT idPermiso from deleted)
DELETE tb_RelUsuarioPermisos FROM tb_RelUsuarioPermisos WHERE idPermiso in (SELECT idPermiso FROM deleted)
DELETE tb_RelGrupoPermisos FROM tb_RelGrupoPermisos WHERE idPermiso in (SELECT idPermiso FROM deleted)

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updatePermisos] ON [dbo].[tb_Permisos]
FOR  UPDATE 
AS
SET NOCOUNT ON
if (update(idPermiso)) begin

UPDATE tb_RelGruposPermisos
SET idPermiso = (SELECT idPermiso from inserted)
WHERE idPermiso in (SELECT idPermiso from deleted)

UPDATE tb_RelUsuarioPermisos
SET idPermiso = (SELECT idPermiso from inserted)
WHERE idPermiso in (SELECT idPermiso from deleted)

UPDATE tb_RelPermisosRoles
SET idPermiso = (SELECT idPermiso from inserted)
WHERE idPermiso in (SELECT idPermiso from deleted)


end

GO
ALTER TABLE [dbo].[tb_Permisos] ADD CONSTRAINT [PK_tb_Permisos] PRIMARY KEY NONCLUSTERED  ([idPermiso]) ON [PRIMARY]
GO
